<?php

namespace Condistelec\MainBundle\Controller;

use Condistelec\MainBundle\Entity\Usuario;
use Condistelec\MainBundle\Entity\Respuesta;
use Condistelec\MainBundle\Entity\Perfil;
use Condistelec\MainBundle\Entity\Registro;

use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Template;

use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use JMS\SecurityExtraBundle\Annotation\Secure;

use PHPExcel;
use PHPExcel_IOFactory;

class AdminHomeController extends Controller
{
	
	private function getNewResponse($filename) {
    	$response = new Response();
    	
    	$response->setContent('<html><body><h1></h1></body></html>');
    	$response->setStatusCode(200);
    	$response->headers->set('Content-Type', 'text/html');
    	 
    	$response->headers->set('Content-Type', 'application/vnd.ms-excel');
    	$response->headers->set('Content-Disposition', 'attachment;filename="'.$filename.'"');
    	$response->headers->set('Cache-Control', 'ax-age=0');
    	
    	return $response;
    }  
	
	/**
	 * @Route("/downloadlist", name = "_cond_app_export_excel")
	 * @Secure(roles="ROLE_ADMIN")
	 * @Template()
	 */
    public function downloadlistAction(Request $request) {
    	$objPHPExcel = new PHPExcel();
    	
       	$activeSheet = $objPHPExcel->setActiveSheetIndex(0);
       
      	$activeSheet->setCellValue('A1', 'Razon Social')
                    ->setCellValue('B1', 'Email')
                    ->setCellValue('C1', 'Celular')
                    ->setCellValue('D1', 'Telefono')
                    ->setCellValue('E1', 'Direccion')
                    ->setCellValue('F1', 'Serie Actual')
                    ->setCellValue('G1', 'Total de Aciertos')
                    ->setCellValue('H1', 'Intentos');
       
       //$users = $this->getDoctrine()->getRepository('CondistelecMainBundle:Usuario')->findBy(array('estado' => 'C','perfil' => 1));
       $users = $this->getDoctrine()->getRepository('CondistelecMainBundle:Usuario')->getUserList('C',1);
       
       if($users) {
       		$i = 2;
       		
       		foreach ($users as $usr) {
       			//die(var_dump($usr->getApellido()));
       			$activeSheet->setCellValue('A' . $i, $usr['apellido'].', '.$usr['nombre'])
       			->setCellValue('B' . $i, $usr['email'])
       			->setCellValue('C' . $i, $usr['celular'])
       			->setCellValue('D' . $i, $usr['telefono'])
       			->setCellValue('E' . $i, $usr['direccion']. ' '.$usr['localidad'])
       			->setCellValue('F' . $i, $usr['serieActual'])
       			->setCellValue('G' . $i, $usr['aciertos'])
            ->setCellValue('H' . $i, $usr['intentos']);
       			$i++;
       		}
       	}
       	
       //$activeSheet->getStyle('A1:E'.i-1)->applyFromArray($styleArray);
      	
       $response = $this->getNewResponse('report-userlist.xls');
       //$response->prepare();
       $response->sendHeaders();
       
       $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
       $objWriter->save('php://output');
       exit();
    }   
    
	/**
	 * @Route("/downloadfinallist", name = "_cond_app_export_final_excel")
	 * @Secure(roles="ROLE_ADMIN")
	 * @Template()
	 */
    public function downloadfinallistAction(Request $request) {
    	$objPHPExcel = new PHPExcel();
    	$em = $this->getDoctrine()->getEntityManager();
    	
    	/*$styleArray = array(
		    'borders' => array(
		        'allborders' => array(
		            'style' => PHPExcel_Style_Border::BORDER_THICK,
		            'color' => array('argb' => 'FFFF0000'),
		        ),
		    ),
		);*/

     	$activeSheet = $objPHPExcel->setActiveSheetIndex(0);
       
      	$activeSheet->setCellValue('A1', 'Porcentajes por Serie');
       
      //$users = $this->getDoctrine()->getRepository('CondistelecMainBundle:Usuario')->findAll();
       $retorno = $em->getRepository('CondistelecMainBundle:Respuesta')->getFinalNotes();
       
       	if($retorno) {
       		$i = 2;
       		$nroSerie = 999;
       		$totalErrores = 0;
       		$totalRespuestas = 0;
       		$palabra = "Simbolo";
       		
       		foreach ($retorno as $ret) {
       			if ($nroSerie != $ret['numero']){
       				if ($nroSerie != 999){
       					$i++;
       					$porcentaje = 100 - (($totalErrores*100)/$totalRespuestas);
       					$activeSheet->setCellValue('A' . $i, "Efectividad Serie Nro ".$nroSerie)
	       				->setCellValue('B' . $i, $porcentaje)
	       				->setCellValue('C' . $i, "%");
	       				$totalErrores = 0;
       					$totalRespuestas = 0;
       					$i++;
       				}
       				$i++;
	       			$activeSheet->setCellValue('A' . $i, "Serie Nro ".$ret['numero']);
	       			$i++;
	       			$nroSerie = $ret['numero'];
	       			$activeSheet->setCellValue('A' . $i, "Pregunta")
	       			->setCellValue('B' . $i, "Total de Respuestas")
	       			->setCellValue('C' . $i, "Total de Errores");
	       			$i++;
       			}

       			$totalErrores = $totalErrores + $ret['cantErrores'];
       			$totalRespuestas = $totalRespuestas + $ret['cantTotal'];
       			if (strpos($ret['descripcion'],$palabra) === false){
       				$activeSheet->setCellValue('A' . $i, $ret['descripcion'])
       				->setCellValue('B' . $i, $ret['cantTotal'])
       				->setCellValue('C' . $i, $ret['cantErrores']);
       			}else{
       				$activeSheet->setCellValue('A' . $i, "De que es este Simbolo?")
       				->setCellValue('B' . $i, $ret['cantTotal'])
       				->setCellValue('C' . $i, $ret['cantErrores']);
       			}
       			
       			$i++;
       		}
       		if ($nroSerie != 999){
				$porcentaje = 100 - (($totalErrores*100)/$totalRespuestas);
       			$activeSheet->setCellValue('A' . $i, "Efectividad")
	       		->setCellValue('B' . $i, $porcentaje)
	       		->setCellValue('C' . $i, "%");
	       		$totalErrores = 0;
       			$totalRespuestas = 0;
       		}
       	}
       	
       //$activeSheet->getStyle('A1:E'.i-1)->applyFromArray($styleArray);
      	
       $response = $this->getNewResponse('report-porcentajes.xls');
       //$response->prepare();
       $response->sendHeaders();
       
       $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
       $objWriter->save('php://output');
       exit();
    }       
    
    
	
	/**
	 * @Route("/list", name = "_cond_app_user_list")
	 * @Secure(roles="ROLE_ADMIN")
	 * @Template()
	 */
    public function listAction(Request $request) {
    	$users = $this->getDoctrine()->getRepository('CondistelecMainBundle:Usuario')->getUserList('C',1);
     	//$clients = $this->getDoctrine()->getRepository('GSBAppBundle:Cliente')->findBy(array('habilitado' => true), array('razonSocial' => 'asc'));
    	
    	return array('users' => $users);
    }
}
